70-761 Querying Data with Transact-SQL: MCSA SQL 2016 exam 70-761 (MCSA: SQL 2016) by Robert Van den Berg

Author:Robert Van den Berg [Van den Berg, Robert]
Language: eng
Format: azw3
Published: 2018-10-19T16:00:00+00:00

There are two reasons to use table variables and temporary tables. The first is simplicity, the second is performance. We will discuss scenarios for both of these benefits in general terms, but won’t be giving any examples here. The reason for this is that such examples would be, by their very nature, quite complex, and require a lot of explanation of both the real world scenario and the query optimizer, with limited applicability on the exam.

Let’s start with simplicity. SQL Server is very good at performing a lot of complex operations in a single statement. However, humans are not, and programmers are humans. So if you need to perform a number of complex operations, it is often easier to perform these operations step-by-step than in one big statement. Performing operations step-by-step results in more readable code, with the added benefit that you can check the intermediate results. You put all the data you need into a temporary table, perform step 1 on that temporary table, check the results, and move on to step 2. And because you’re working on a temporary table (or table variable), your code is isolated from the rest of the processes on the database. Breaking complex logic into multiple steps might be bad for performance, but this is a trade-off you might be willing to consider; code that is unreadable is unmaintainable code, and therefore bad code.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.